import pandas as pd
import pymysql

def load():
    path = r'djangoProject/source/form.xlsx'
    df = pd.read_excel(path)
    values = df.values.tolist()
    keys = ['name1', 'name2', 'name3', 'level', 'story']
    cur,conn = connectdb()
    for i in range(1,len(values)-1):
        item = values[i]
        insert_data(cur,keys,item)

    data = cur.execute('select * from need_list')
    print(data)
    cur.close()
    conn.close()



def connectdb():
    # 数据库连接配置
    config = {
        'host': 'localhost',
        'user': 'root',
        'password': '12345678',
        'database': 'test',
        'charset': 'utf8mb4',
    }

    # 字段名称及其对应的数据类型字典
    columns_dict = {
        'name1': 'VARCHAR(255)',
        'name2': 'VARCHAR(255)',
        'name3': 'VARCHAR(255)',
        'level': 'VARCHAR(255)',
        'story': 'VARCHAR(255)',
        # 'children': 'JSON'  # 假设这是一个字典或数组
    }
    conn = pymysql.connect(**config)
    cur = conn.cursor()
    # 构建CREATE TABLE语句
    table_name = 'need_list'
    column_definitions = ', '.join([f"{key} {value}" for key, value in columns_dict.items()])
    create_table_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({column_definitions})"

    # 执行SQL语句
    cur.execute(create_table_sql)

    # 提交事务
    conn.commit()
    return cur,conn

def insert_data(cur, keys,values):
    sql_template = "INSERT INTO need_list ({}) VALUES ({})".format(','.join(keys),
                                                              ','.join(['%s'] * len(keys)))
    cur.execute(sql_template,values)
